CREATE SEQUENCE RELEASE_COUNT INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE TRACK_COUNT INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE HUMAN_COUNT INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE PERFORMER_COUNT INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE ALIAS_COUNT INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE LABEL_COUNT INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE FORMAT_COUNT INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE GENRE_COUNT INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE USER_COUNT INCREMENT BY 1 START WITH 1;

CREATE TABLE "LABEL"
(
	"ID" NUMBER(6,0) NOT NULL,
	"TITLE" VARCHAR2 (50 CHAR) NOT NULL,
	"BIRTHDAY" DATE,
	"DEATHDAY" DATE,
	"COUNTRY" VARCHAR2(30 CHAR), 
	"PARENT_ID" NUMBER(6,0),
	"CREATOR_ID" NUMBER(6,0) NOT NULL,
	"INFO" VARCHAR(4000),
	CONSTRAINT "LABEL_PK" PRIMARY KEY ("ID"),
	CONSTRAINT "LABEL_FK" FOREIGN KEY ("PARENT_ID")
	REFERENCES "LABEL" ("ID"),
	CONSTRAINT "LABEL_FK2" FOREIGN KEY ("CREATOR_ID")
	REFERENCES "USER" ("ID"),
	CONSTRAINT "LABEL_CHECK" CHECK (BIRTHDAY <= DEATHDAY)
);
/

CREATE TABLE  "RELEASE" 
(	
	"ID" NUMBER(7,0) NOT NULL, 
	"CATALOG" VARCHAR2(20 CHAR) NOT NULL,
	"TITLE" VARCHAR2(100 CHAR) NOT NULL,
	"RELEASED" DATE, 
	"COUNTRY" VARCHAR2(30 CHAR),
	"CREATOR_ID" NUMBER(6,0) NOT NULL,	
	"INFO" VARCHAR2(4000),
	CONSTRAINT "RELEASE_PK" PRIMARY KEY ("ID"),
	CONSTRAINT "RELEASE_FK" FOREIGN KEY ("CREATOR_ID")
	REFERENCES "USER" ("ID")
)
/

CREATE TABLE  "TRACK" 
(	
	"ID" NUMBER(9,0) NOT NULL, 
	"TITLE" VARCHAR2(100 CHAR),
	"RELEASE_ID" NUMBER(8,0),
	"POSITION" NUMBER(3,0),
	"LENGTH" NUMBER(5,0),
	"DISC" NUMBER(2,0),
	"CREATOR_ID" NUMBER(6,0) NOT NULL,	
	"INFO" VARCHAR2(4000),
	CONSTRAINT "TRACK_PK" PRIMARY KEY ("ID"), 
	CONSTRAINT "TRACK_FK" FOREIGN KEY ("RELEASE_ID")
	REFERENCES  "RELEASE" ("ID"),
	CONSTRAINT "TRACK_FK2" FOREIGN KEY ("CREATOR_ID")
	REFERENCES "USER" ("ID")
);
/

CREATE TABLE "HUMAN"
(
	"ID" NUMBER(7,0) NOT NULL,
	"NAME" VARCHAR2(100 CHAR),
	"BIRTHDAY" DATE,
	"DEATHDAY" DATE,
	"COUNTRY" VARCHAR2(30 CHAR), 
	"CREATOR_ID" NUMBER(6,0) NOT NULL,	
	"INFO" VARCHAR2(4000),
	CONSTRAINT "HUMAN_PK" PRIMARY KEY("ID"),
	CONSTRAINT "HUMAN_FK" FOREIGN KEY ("CREATOR_ID")
	REFERENCES "USER" ("ID"),
	CONSTRAINT "HUMAN_CHECK" CHECK (BIRTHDAY <= DEATHDAY)
);
/

CREATE TABLE "PERFORMER"
(
	"ID" NUMBER(7,0) NOT NULL,
	"TITLE" VARCHAR(100 CHAR) NOT NULL,
	"BIRTHDAY" DATE,
	"DEATHDAY" DATE,
	"COUNTRY" VARCHAR2(30 CHAR), 
	"CREATOR_ID" NUMBER(6,0) NOT NULL,	
	"INFO" VARCHAR(4000),
	CONSTRAINT "PERFORMER_PK" PRIMARY KEY ("ID"),
	CONSTRAINT "PERFORMER_FK" FOREIGN KEY ("CREATOR_ID")
	REFERENCES "USER" ("ID"),
	CONSTRAINT "PERFORMER_CHECK" CHECK (BIRTHDAY <= DEATHDAY)
);
/

CREATE TABLE "COMPOSITION"
(
	"HUMAN_ID" NUMBER(7,0) NOT NULL,
	"PERFORMER_ID" NUMBER(7,0) NOT NULL,
	"JOIN" DATE,
	"LEAVE" DATE,
	"ROLE" VARCHAR2(50 CHAR),
	"ALIAS_ID" NUMBER(8,0),
	"CREATOR_ID" NUMBER(6,0) NOT NULL,	
	"INFO" VARCHAR(4000),
	CONSTRAINT "COMPOSITION_PK" PRIMARY KEY ("HUMAN_ID", "PERFORMER_ID"),
	CONSTRAINT "COMPOSITION_FK" FOREIGN KEY ("PERFORMER_ID")
	REFERENCES "PERFORMER" ("ID"),
	CONSTRAINT "COMPOSITION_FK2" FOREIGN KEY ("HUMAN_ID")
	REFERENCES "HUMAN" ("ID"),
	CONSTRAINT "COMPOSITION_FK4" FOREIGN KEY ("CREATOR_ID")
	REFERENCES "USER" ("ID"),
	CONSTRAINT "COMPOSITION_FK3" FOREIGN KEY ("ALIAS_ID")
	REFERENCES "ALIAS" ("ID") ON DELETE SET NULL,
	CONSTRAINT "COMPOSITION_CHECK" CHECK (JOIN <= LEAVE)
);
/

CREATE TABLE "USER"
( 
	"ID" NUMBER(6,0) NOT NULL,
	"HUMAN_ID" NUMBER(7,0) NOT NULL UNIQUE,
    "NICKNAME" VARCHAR2(50 CHAR) NOT NULL UNIQUE,
	"STATUS" NUMBER(1,0) NOT NULL,
	"REGISTERED" DATE,
	CONSTRAINT "USER_PK" PRIMARY KEY ("ID"),
	CONSTRAINT "USER_FK" FOREIGN KEY ("HUMAN_ID")
	REFERENCES "HUMAN" ("ID")
);
/

CREATE TABLE "FORMAT"
(
	"ID" NUMBER(3,0) NOT NULL,
	"TITLE" VARCHAR2(20 CHAR) NOT NULL UNIQUE,
	"INFO" VARCHAR(4000),
	CONSTRAINT "FORMAT_PK" PRIMARY KEY ("ID")
);

CREATE TABLE "GENRE"
(
	"ID" NUMBER(5,0) NOT NULL,
	"TITLE" VARCHAR(50 CHAR) NOT NULL UNIQUE,
	"INFO" VARCHAR(4000),
	CONSTRAINT "GENRE_PK" PRIMARY KEY ("ID")
);

CREATE OR REPLACE TRIGGER RELEASE_BIR
	BEFORE INSERT ON "RELEASE"
	FOR EACH ROW
	BEGIN
		SELECT RELEASE_COUNT.NEXTVAL INTO :new.ID FROM dual;
	END RELEASE_BIR;
   /

	CREATE OR REPLACE TRIGGER HUMAN_BIUR
	BEFORE INSERT OR UPDATE ON HUMAN
	FOR EACH ROW
	BEGIN
		:new.NAME := name_format (:new.NAME);
		IF INSERTING THEN
			SELECT HUMAN_COUNT.NEXTVAL INTO :new.ID FROM dual;
		END IF;
	END HUMAN_BIUR;
   /

CREATE OR REPLACE TRIGGER PERFORMER_BIR
	BEFORE INSERT ON PERFORMER
	FOR EACH ROW
	BEGIN
		SELECT PERFORMER_COUNT.NEXTVAL INTO :new.ID FROM dual;
	END PERFROMER_BIR;
	/

CREATE OR REPLACE TRIGGER ALIAS_BIR
	BEFORE INSERT ON ALIAS
	FOR EACH ROW
	BEGIN
		SELECT ALIAS_COUNT.NEXTVAL INTO :new.ID FROM dual;
	END ALIAS_BIR;
	/

	CREATE OR REPLACE TRIGGER COMPOSITION_BIUR
	BEFORE INSERT OR UPDATE ON COMPOSITION
	FOR EACH ROW
	DECLARE
	pBD DATE;
	hBD DATE;
	pDD DATE;
	hDD DATE;
	wrong_date EXCEPTION;
	BEGIN
		SELECT PERFORMER.BIRTHDAY INTO pBD FROM PERFORMER WHERE PERFORMER.ID = :new.PERFORMER_ID;
		SELECT HUMAN.BIRTHDAY INTO hBD FROM HUMAN WHERE HUMAN.ID = :new.HUMAN_ID;
		SELECT PERFORMER.DEATHDAY INTO pDD FROM PERFORMER WHERE PERFORMER.ID = :new.PERFORMER_ID;
		SELECT HUMAN.DEATHDAY INTO hDD FROM HUMAN WHERE HUMAN.ID = :new.HUMAN_ID;
		IF :new.JOIN > pDD 
		OR :new.JOIN < pBD
		OR :new.JOIN > hDD
		OR :new.JOIN < hBD
		OR :new.LEAVE > pDD
		OR :new.LEAVE < pBD
		OR :new.LEAVE > hDD
		OR :new.LEAVE < hBD THEN
			RAISE wrong_date;
		END IF;
	EXCEPTION
		WHEN wrong_date THEN RAISE_APPLICATION_ERROR(-20040, 'ERROR !');
	END COMPOSITION_BIUR;
	/

	CREATE OR REPLACE TRIGGER LABEL_BIR
	BEFORE INSERT ON LABEL
	FOR EACH ROW
	BEGIN
		SELECT LABEL_COUNT.NEXTVAL INTO :new.ID FROM dual;
	END LABEL_BIR;
	/

CREATE OR REPLACE TRIGGER FORMAT_BIR
	BEFORE INSERT ON "FORMAT"
	FOR EACH ROW
	BEGIN
		SELECT FORMAT_COUNT.NEXTVAL INTO :new.ID FROM dual;
	END FORMAT_BIR;	
	/

   CREATE OR REPLACE TRIGGER GENRE_BIR
	BEFORE INSERT ON "GENRE"
	FOR EACH ROW
	BEGIN
		SELECT GENRE_COUNT.NEXTVAL INTO :new.ID FROM dual;
	END GENRE_BIR;
	/

CREATE OR REPLACE TRIGGER TRACK_BIR
	BEFORE INSERT ON TRACK
	FOR EACH ROW
	BEGIN
		SELECT TRACK_COUNT.NEXTVAL INTO :new.ID FROM dual;
	END TRACK_BIR;
	/
	
	CREATE OR REPLACE TRIGGER USER_BIR
	BEFORE INSERT ON "USER"
	FOR EACH ROW
	BEGIN
		SELECT USER_COUNT.NEXTVAL INTO :new.ID FROM dual;
	END USER_BIR;
	/